package com.ims.repository;

import com.b2c.common.third.thymeleaf.PagingResponse;
import com.ims.entity.MediaDataReportEntity;
import com.ims.entity.MediaDataReportItemEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class MediaDataReportRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional
    public void addData(Integer mediaAccountId, Integer userCount, String summary, String improvement,String reportPeriod, List<MediaDataReportItemEntity> dataItems){
        try {
            /**************更新账号基本信息*****************/
            jdbcTemplate.update("UPDATE media_account SET userCount=?,updateTime=? WHERE id=?", userCount, System.currentTimeMillis() / 1000, mediaAccountId);

            /*************插入media_data_report**************/
            String sql = "INSERT INTO media_data_report (accountId,userCount,reportTime,summary,improvement,reportPeriod) VALUE (?,?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setInt(1, mediaAccountId);
                    ps.setInt(2, userCount);
                    ps.setLong(3, System.currentTimeMillis() / 1000);
                    ps.setString(4, summary);
                    ps.setString(5, improvement);
                    ps.setString(6, reportPeriod);
                    return ps;
                }
            }, keyHolder);

            Integer reportId = keyHolder.getKey().intValue();


            /*************插入media_data_report_item**************/
            String sql1 = "INSERT INTO media_data_report_item (reportId,publishDate,viewCount,zanCount,commentCount) VALUE(?,?,?,?,?)";
            for (var item : dataItems) {
                jdbcTemplate.update(sql1, reportId, item.getPublishDate(), item.getViewCount(), item.getZanCount(), item.getCommentCount());
            }
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }

    }

    @Transactional
    public Integer addData(MediaDataReportEntity reportEntity){
        try {
            /**************更新账号基本信息*****************/
            jdbcTemplate.update("UPDATE media_account SET userCount=?,updateTime=? WHERE id=?", reportEntity.getUserCount(), System.currentTimeMillis() / 1000, reportEntity.getAccountId());

            /*************插入media_data_report**************/
            String sql = "INSERT INTO media_data_report (accountId,userCount,reportTime,reportUserId,summary,improvement,reportPeriod) VALUE (?,?,?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setInt(1, reportEntity.getAccountId());
                    ps.setInt(2, reportEntity.getUserCount());
                    ps.setLong(3, System.currentTimeMillis() / 1000);
                    ps.setInt(4, reportEntity.getReportUserId());
                    ps.setString(5, reportEntity.getSummary());
                    ps.setString(6, reportEntity.getImprovement());
                    ps.setString(7, reportEntity.getPublishDate());
                    return ps;
                }
            }, keyHolder);

            Integer reportId = keyHolder.getKey().intValue();


            /*************插入media_data_report_item**************/
            String sql1 = "INSERT INTO media_data_report_item (reportId,publishDate,viewCount,zanCount,commentCount) VALUE(?,?,?,?,?)";
            jdbcTemplate.update(sql1, reportId, reportEntity.getPublishDate(), reportEntity.getViewCount(), reportEntity.getZanCount(), reportEntity.getCommentCount());

            return  reportId;

        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return null;
        }

    }

    public void editData(Integer id, Integer userCount, String summary, String improvement, String reportPeriod, List<MediaDataReportItemEntity> dataItems) {
        try {
            /**************更新基本信息*****************/
            /*************media_data_report**************/
            String sql = "UPDATE  media_data_report SET userCount=?,summary=?,improvement=?,reportPeriod=?,updateTime=? WHERE id=?";
            jdbcTemplate.update(sql,userCount,summary,improvement,reportPeriod,System.currentTimeMillis() / 1000,id);


            /*************更新media_data_report_item**************/
            String sql1 = "UPDATE media_data_report_item SET publishDate=?,viewCount=?,zanCount=?,commentCount=? WHERE id=?";
            for (var item : dataItems) {
                jdbcTemplate.update(sql1, item.getPublishDate(), item.getViewCount(), item.getZanCount(), item.getCommentCount(),item.getId());
            }
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }
    }

    @Transactional
    public PagingResponse<MediaDataReportEntity> getList(int pageIndex, int pageSize, Integer mediaAccountId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS a.*,b.name as accountName ");
        sb.append(",(SELECT COUNT(0) FROM media_data_report_item WHERE reportId=a.id) as contentCount");
        sb.append(",(SELECT SUM(viewCount) FROM media_data_report_item WHERE reportId=a.id) as viewCount");
        sb.append(",(SELECT SUM(zanCount) FROM media_data_report_item WHERE reportId=a.id) as zanCount");
        sb.append(",(SELECT SUM(commentCount) FROM media_data_report_item WHERE reportId=a.id) as commentCount");
        sb.append(" FROM media_data_report a LEFT JOIN media_account b ON b.id=a.accountId");
        sb.append(" where 1=1 ");
        List<Object> params = new ArrayList<>();


        if (mediaAccountId!=null && mediaAccountId > 0) {
            sb.append(" AND accountId =? ");
            params.add(mediaAccountId);
        }

        sb.append(" ORDER BY id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<>(MediaDataReportEntity.class),params.toArray(new Object[params.size()]));

        int total = jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);

//        for (var item:list) {
//            item.setItems(jdbcTemplate.query("SELECT * FROM media_data_report_item WHERE reportId=?",new BeanPropertyRowMapper<>(MediaDataReportItemEntity.class),item.getId()));
//        }

        return new PagingResponse<>(pageIndex, pageSize, total, list);
    }


    public MediaDataReportEntity getById(Integer id) {
        try{
            var entity = jdbcTemplate.queryForObject("SELECT a.*,b.name as accountName FROM media_data_report a LEFT JOIN media_account b ON b.id=a.accountId WHERE a.id=?",new BeanPropertyRowMapper<>(MediaDataReportEntity.class),id);
            entity.setItems(jdbcTemplate.query("SELECT * FROM media_data_report_item WHERE reportId=?",new BeanPropertyRowMapper<>(MediaDataReportItemEntity.class),id));
            return entity;
        }catch (Exception e) {
            return null;
        }
    }
}
